package com.yh.dao.impl;

import com.yh.dao.HouseDao;
import com.yh.dao.JDBCUtil;
import com.yh.dao.common.HibernateUtil;
import com.yh.model.HouseModel;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;

import javax.transaction.UserTransaction;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class HouseDaoimpl implements HouseDao {
    private JDBCUtil jdbc = new JDBCUtil();
    @Override
    public int addHouse(HouseModel houseEntity) {
        int a = -1;
//        try {
//            a = jdbc.ExcuteUpdate("insert into house values (null,?,?,?,?,?,?,?,?,?)",houseEntity.getTitle(),houseEntity.getType(),
//                    houseEntity.getArea(),houseEntity.getPrice(),houseEntity.getDate(),houseEntity.getTown(),
//                    houseEntity.getStreet(),houseEntity.getPhone(),houseEntity.getContext());
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
        Query query = HibernateUtil.currentSession().createSQLQuery("insert into house values (null,?,?,?,?,?,?,?,?,?,?)");
        query.setParameter(0,houseEntity.getTitle()).setParameter(1,houseEntity.getType()).setParameter(2,houseEntity.getArea()).
                setParameter(3,houseEntity.getPrice()).setParameter(4,houseEntity.getDate()).setParameter(5,houseEntity.getTown()).
                setParameter(6,houseEntity.getStreet()).setParameter(7,houseEntity.getPhone()).setParameter(8,houseEntity.getContext()).
                setParameter(9,houseEntity.getHid());
        a = query.executeUpdate();
        return a;
    }

    @Override
    public int delHouse(int hid) {
        int a = -1;
//        try {
//            a = jdbc.ExcuteUpdate("delete from house where hid = ?",hid);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
        Query query = HibernateUtil.currentSession().createQuery("delete from HouseModel where hid = ?");
        query.setParameter(0,hid);
        a = query.executeUpdate();
        return a;
    }

    @Override
    public int updHouse(HouseModel houseEntity) {
        int a = -1;
//        try {
//            a = jdbc.ExcuteUpdate("update house set title = '"+houseEntity.getTitle()+"',type = '"+houseEntity.getType()+"',area = ?,price = ?,date = '"+houseEntity.getDate()+"',town = '"+houseEntity.getTown()+"',\n" +
//                    "                 street = '"+houseEntity.getStreet()+"',phone = '"+houseEntity.getPhone()+"',context = '"+houseEntity.getContext()+"' where hid = ?",houseEntity.getArea(),
//                    houseEntity.getPrice(),houseEntity.getHid());
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
        Query query = HibernateUtil.currentSession().createSQLQuery("update house set title = '"+houseEntity.getTitle()+"',type = '"+houseEntity.getType()+"',area = ?,price = ?,date = '"+houseEntity.getDate()+"',town = '"+houseEntity.getTown()+"',\n"+
                "street = '"+houseEntity.getStreet()+"',phone = '"+houseEntity.getPhone()+"',context = '"+houseEntity.getContext()+"' where hid = ?");
        query.setParameter(0,houseEntity.getArea()).setParameter(1,houseEntity.getPrice()).setParameter(2,houseEntity.getHid());
         a = query.executeUpdate();
        return a;
    }

    @Override
    public List<HouseModel> allHouse(int first,int end) {
//        List<HouseEntity> list = new ArrayList<>();
//        try {
//            ResultSet rs = jdbc.Find("select * from house",null);
//            while(rs.next()){
//                list.add(new HouseEntity(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),
//                        rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),
//                        rs.getString(9),rs.getString(10)));
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        List<HouseModel> list = HibernateUtil.currentSession().getNamedQuery("findall").list();
        Criteria criteria = HibernateUtil.currentSession().createCriteria(HouseModel.class);
        criteria.setFirstResult(first);criteria.setMaxResults(end);
        return criteria.list();
    }

    public int count(){
        return HibernateUtil.currentSession().createCriteria(HouseModel.class).list().size();
    }

    @Override
    public HouseModel findHouse(int hid) {
//        List<HouseEntity> list = new ArrayList<>();
//        try {
//            ResultSet rs = jdbc.Find("select * from house where hid = ?",hid);
//            while(rs.next()){
//                list.add(new HouseEntity(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),
//                        rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),
//                        rs.getString(9),rs.getString(10)));
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        HouseModel house =  (HouseModel) HibernateUtil.currentSession().get(HouseModel.class,hid);
        Criteria cr = HibernateUtil.currentSession().createCriteria(HouseModel.class);
        cr.add(Restrictions.eq("hid",hid));
        HouseModel house = (HouseModel) cr.uniqueResult();
        return house;
    }

    @Override
    public List<HouseModel> likeHouse(String title,String type,String area,String price,String street) {
//        List<HouseEntity> list = new ArrayList<>();
//        try {
//            ResultSet rs = jdbc.Find("select * from house where title like '%"+title+"%'",null);
//            while(rs.next()){
//                list.add(new HouseEntity(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),
//                        rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),
//                        rs.getString(9),rs.getString(10)));
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        Query query = HibernateUtil.currentSession().createQuery("from HouseModel where title like '%"+title+"%'");
//        query.setParameter(0,title);
//        List<HouseModel> list = query.list();
        String hql = "from HouseModel where title like '%"+title+"%'";
        if (type != "" && type != null){
            if (!type.equals("0") && type != "") {
                hql += " and houseTypeModel.tid = " + type + "";
            }
        }
        if (street != "" && street != null){
            if (!street.equals("0") && street != ""){
                hql += " and streetModel.sid = "+street+"";
            }
        }
        if (area != "" && area != null){
            if (area.equals("1") || area == "1"){
                hql += " and area < 40";
            }
            if (area.equals("2") || area == "2"){
                hql += " and area between 40 and 500";
            }
            if (area.equals("3") || area == "3"){
                hql += " and area > 500";
            }
        }
        if (price != "" && price != null){
            if (price.equals("1") || price == "1"){
                hql += " and price < 100";
            }
            if (price.equals("2") || price == "2"){
                hql += " and price between 100 and 500";
            }
            if (price.equals("3") || price == "3"){
                hql += " and price > 500";
            }
        }
        System.out.println(hql);
        List<HouseModel> list = HibernateUtil.currentSession().createQuery(hql).list();
        return list;
    }

    public int userHousecount(int id){
        Criteria cr = HibernateUtil.currentSession().createCriteria(HouseModel.class);
        cr.add(Restrictions.eq("userid",id));
        return cr.list().size();
    }


    @Override
    public List<HouseModel> userHouses(int id,int first,int end) {
        Criteria cr = HibernateUtil.currentSession().createCriteria(HouseModel.class);
        cr.add(Restrictions.eq("userid",id));
        cr.setFirstResult(first);cr.setMaxResults(end);
        return cr.list();
    }
}
